{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "55b5fd34",
   "metadata": {},
   "source": [
    "## 数据合并需求"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a986bc91",
   "metadata": {},
   "source": [
    "在实际的业务中，需要将多个文档、数据，可能是 Series 或 DataFrame 拼合在一起，进行大数据分析。  \n",
    "Pandas 提供的各种功能轻而易举地进行这些工作。\n",
    "\n",
    "应用场景\n",
    "在作者来看，在以下场景下可能会需要对数据进行拼接、合并操作：\n",
    "\n",
    "日常工作中由多方交付的表格要合并成一个总表格\n",
    "从数据库导出数据，单次太大，多次导出后需要合并\n",
    "有多个数据，需要制取各自中有用的信息组成一个新的表\n",
    "等等\n",
    "操作\n",
    "数据的合并其实有合并、连接、拼接等几种，最简单的是连接。连接字段相同只是把新的内容追加在后边。合并可能是不同的列，把这些列组合在一起形成多个列。还有一种是混合的，在以上的基础上，合并过程中还需要做些计算。\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "783c2b18",
   "metadata": {},
   "source": [
    "## 数据准备"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "31c7d558",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:22:47.754330Z",
     "start_time": "2022-05-16T06:22:47.747880Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f34af75c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:38:01.652648Z",
     "start_time": "2022-05-16T06:38:01.616802Z"
    }
   },
   "outputs": [],
   "source": [
    "# 以下有三个 df ，后续的操作我们将对他们进行合并处理。\n",
    "\n",
    "df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                    'B': ['B0', 'B1', 'B2', 'B3'],\n",
    "                    'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                    'D': ['D0', 'D1', 'D2', 'D3']},\n",
    "                   index=[0, 1, 2, 3])\n",
    "\n",
    "\n",
    "df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],\n",
    "                    'B': ['B4', 'B5', 'B6', 'B7'],\n",
    "                    'C': ['C4', 'C5', 'C6', 'C7'],\n",
    "                    'D': ['D4', 'D5', 'D6', 'D7']},\n",
    "                   index=[4, 5, 6, 7])\n",
    "\n",
    "\n",
    "df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10', 'A11'],\n",
    "                    'B': ['B8', 'B9', 'B10', 'B11'],\n",
    "                    'C': ['C8', 'C9', 'C10', 'C11'],\n",
    "                    'D': ['D8', 'D9', 'D10', 'D11']},\n",
    "                   index=[8, 9, 10, 11])\n",
    "\n",
    "df4 = pd.DataFrame({'B': ['B2', 'B3', 'B6', 'B7'],\n",
    "                   'D': ['D2', 'D3', 'D6', 'D7'],\n",
    "                   'F': ['F2', 'F3', 'F6', 'F7']},\n",
    "                  index = [2,3,6,7])\n",
    "\n",
    "s1 = pd.Series(['X0', 'X1', 'X2', 'X3'], name='X')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e6a28844",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:22:49.619110Z",
     "start_time": "2022-05-16T06:22:49.604783Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df1)  # df1是DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f07d8abe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D\n",
       "0  A0  B0  C0  D0\n",
       "1  A1  B1  C1  D1\n",
       "2  A2  B2  C2  D2\n",
       "3  A3  B3  C3  D3"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "79ea556e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>B4</td>\n",
       "      <td>C4</td>\n",
       "      <td>D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>B5</td>\n",
       "      <td>C5</td>\n",
       "      <td>D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>B6</td>\n",
       "      <td>C6</td>\n",
       "      <td>D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A7</td>\n",
       "      <td>B7</td>\n",
       "      <td>C7</td>\n",
       "      <td>D7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D\n",
       "4  A4  B4  C4  D4\n",
       "5  A5  B5  C5  D5\n",
       "6  A6  B6  C6  D6\n",
       "7  A7  B7  C7  D7"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d83a1fe3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A8</td>\n",
       "      <td>B8</td>\n",
       "      <td>C8</td>\n",
       "      <td>D8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A9</td>\n",
       "      <td>B9</td>\n",
       "      <td>C9</td>\n",
       "      <td>D9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A10</td>\n",
       "      <td>B10</td>\n",
       "      <td>C10</td>\n",
       "      <td>D10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A11</td>\n",
       "      <td>B11</td>\n",
       "      <td>C11</td>\n",
       "      <td>D11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A    B    C    D\n",
       "8    A8   B8   C8   D8\n",
       "9    A9   B9   C9   D9\n",
       "10  A10  B10  C10  D10\n",
       "11  A11  B11  C11  D11"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0abe3f9a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B2</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B3</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B6</td>\n",
       "      <td>D6</td>\n",
       "      <td>F6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B7</td>\n",
       "      <td>D7</td>\n",
       "      <td>F7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    B   D   F\n",
       "2  B2  D2  F2\n",
       "3  B3  D3  F3\n",
       "6  B6  D6  F6\n",
       "7  B7  D7  F7"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "897928fb",
   "metadata": {},
   "source": [
    "## concat()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aaf64605",
   "metadata": {},
   "source": [
    "### DataFrame合并"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e959b5a5",
   "metadata": {},
   "source": [
    "#### df取并集"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6f169ff",
   "metadata": {},
   "source": [
    "##### df列相同进行连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "73dcc7fc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:51:57.708745Z",
     "start_time": "2022-05-16T06:51:57.658723Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">df1</th>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">df2</th>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>B4</td>\n",
       "      <td>C4</td>\n",
       "      <td>D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>B5</td>\n",
       "      <td>C5</td>\n",
       "      <td>D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>B6</td>\n",
       "      <td>C6</td>\n",
       "      <td>D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A7</td>\n",
       "      <td>B7</td>\n",
       "      <td>C7</td>\n",
       "      <td>D7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">df3</th>\n",
       "      <th>8</th>\n",
       "      <td>A8</td>\n",
       "      <td>B8</td>\n",
       "      <td>C8</td>\n",
       "      <td>D8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A9</td>\n",
       "      <td>B9</td>\n",
       "      <td>C9</td>\n",
       "      <td>D9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A10</td>\n",
       "      <td>B10</td>\n",
       "      <td>C10</td>\n",
       "      <td>D10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A11</td>\n",
       "      <td>B11</td>\n",
       "      <td>C11</td>\n",
       "      <td>D11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A    B    C    D\n",
       "df1 0    A0   B0   C0   D0\n",
       "    1    A1   B1   C1   D1\n",
       "    2    A2   B2   C2   D2\n",
       "    3    A3   B3   C3   D3\n",
       "df2 4    A4   B4   C4   D4\n",
       "    5    A5   B5   C5   D5\n",
       "    6    A6   B6   C6   D6\n",
       "    7    A7   B7   C7   D7\n",
       "df3 8    A8   B8   C8   D8\n",
       "    9    A9   B9   C9   D9\n",
       "    10  A10  B10  C10  D10\n",
       "    11  A11  B11  C11  D11"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "frames = [df1, df2, df3] \n",
    "frames\n",
    "type(frames)  # frames 是 list\n",
    "\n",
    "df = pd.concat(frames) \n",
    "df = pd.concat([df1, df2, df3]) # 将三个有相同列的表合并到一起，并使用新的自然索引\n",
    "df\n",
    "# type(df)  # df是DataFrame\n",
    "\n",
    "df = pd.concat(frames, keys=['df1','df2','df3']) \n",
    "# df = pd.concat([df1,df2,df3], keys = ['x','y','z']) # keys= 给每个表给一个一级索引，形成多层索引\n",
    "# df = pd.concat({'x':df1, 'y':df2, 'z':df3}) # 使用字典，进行索引\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "917a23ed",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:51:57.989001Z",
     "start_time": "2022-05-16T06:51:57.977539Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.concat([df1,df4], ignore_index=True, sort=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9704dae3",
   "metadata": {},
   "source": [
    "##### df列不同进行连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "3cd27db0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:33:51.502354Z",
     "start_time": "2022-05-16T06:33:51.485276Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.concat([df1,df4], axis=1, sort=False)  # df1 列为ABCD，df4列为BDF， 结果列为 ABCDBDF\n",
    "# df = pd.concat([df1,df4], axis=1, join='outer', sort=False)  # join = 'outer',表示取并集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a1fd0e9b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:33:52.005458Z",
     "start_time": "2022-05-16T06:33:51.969398Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "      <td>B2</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "      <td>B3</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B6</td>\n",
       "      <td>D6</td>\n",
       "      <td>F6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B7</td>\n",
       "      <td>D7</td>\n",
       "      <td>F7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B    C    D    B    D    F\n",
       "0   A0   B0   C0   D0  NaN  NaN  NaN\n",
       "1   A1   B1   C1   D1  NaN  NaN  NaN\n",
       "2   A2   B2   C2   D2   B2   D2   F2\n",
       "3   A3   B3   C3   D3   B3   D3   F3\n",
       "6  NaN  NaN  NaN  NaN   B6   D6   F6\n",
       "7  NaN  NaN  NaN  NaN   B7   D7   F7"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e276759",
   "metadata": {},
   "source": [
    "#### df取交集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "ab5c382d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:34:52.944658Z",
     "start_time": "2022-05-16T06:34:52.920696Z"
    }
   },
   "outputs": [],
   "source": [
    "#  \n",
    "df = pd.concat([df1, df4], axis=1, join='inner') # join = 'inner'表示取并集,只将有共同索引的内容进行了合并。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "9a87bcfe",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:34:57.494131Z",
     "start_time": "2022-05-16T06:34:57.467494Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "      <td>B2</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "      <td>B3</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D   B   D   F\n",
       "2  A2  B2  C2  D2  B2  D2  F2\n",
       "3  A3  B3  C3  D3  B3  D3  F3"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f182587b",
   "metadata": {},
   "source": [
    "#### df只取指定索引内容"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4d62539b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "      <td>B2</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "      <td>B3</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D    B    D    F\n",
       "0  A0  B0  C0  D0  NaN  NaN  NaN\n",
       "1  A1  B1  C1  D1  NaN  NaN  NaN\n",
       "2  A2  B2  C2  D2   B2   D2   F2\n",
       "3  A3  B3  C3  D3   B3   D3   F3"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 如果我们只需要第一张表索引的内容，可以：\n",
    "\n",
    "# 以下两个方法效果一样\n",
    "pd.concat([df1, df4], axis=1).reindex(df1.index)\n",
    "pd.concat([df1, df4.reindex(df1.index)], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cad2d274",
   "metadata": {},
   "source": [
    "### Series和DataFrame合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "0ba53e4a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:42:05.699482Z",
     "start_time": "2022-05-16T06:42:05.679345Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.concat([df1, s1], axis=1)  \n",
    "df = pd.concat([df1, s1], axis=1, ignore_index=True)  # ignore_index会取消列名，变成0，1，2，3，4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "ec5fb448",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:42:07.245238Z",
     "start_time": "2022-05-16T06:42:07.205860Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "      <td>X0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "      <td>X1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "      <td>X2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "      <td>X3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    0   1   2   3   4\n",
       "0  A0  B0  C0  D0  X0\n",
       "1  A1  B1  C1  D1  X1\n",
       "2  A2  B2  C2  D2  X2\n",
       "3  A3  B3  C3  D3  X3"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0c7de33",
   "metadata": {},
   "source": [
    "### Series合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "4ed782b6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:48:25.396512Z",
     "start_time": "2022-05-16T06:48:25.369958Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>red</th>\n",
       "      <th>yellow</th>\n",
       "      <th>blue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   red  yellow  blue\n",
       "0    0       0     0\n",
       "1    1       1     1\n",
       "2    2       2     4\n",
       "3    3       3     5"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 多个Series合并为一个DataFrame\n",
    "s3 = pd.Series([0, 1, 2, 3], name='foo') # 有列名\n",
    "s4 = pd.Series([0, 1, 2, 3]) # 无列名\n",
    "s5 = pd.Series([0, 1, 4, 5]) # 无列名\n",
    "\n",
    "pd.concat([s3,s4,s5],axis=1) # \n",
    "pd.concat([s3,s4,s5],axis=1,keys=['red','yellow','blue']) # keys=, 赋予新的列名"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37bd7ac0",
   "metadata": {},
   "source": [
    "## merge()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "2a8a906c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:56:59.031393Z",
     "start_time": "2022-05-16T06:56:59.025418Z"
    }
   },
   "outputs": [],
   "source": [
    "# Pandas 提供了 merge() 方法，具有全功能、高性能的内存连接操作，与 SQL 等关系数据库非常相似。\n",
    "# 这些方法的性能比其他开源实现（如 R 语言中的 base::merge.data.frame）要好得多（在某些情况下甚至超过一个数量级）。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c960ace",
   "metadata": {},
   "source": [
    "### 语法结构"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "2f3d5e48",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T06:57:13.123155Z",
     "start_time": "2022-05-16T06:57:13.113327Z"
    }
   },
   "outputs": [],
   "source": [
    "# 可以将两个 DataFrame 或者 Series 进行连接：\n",
    "\n",
    "# pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,\n",
    "#          left_index=False, right_index=False, sort=True,\n",
    "#          suffixes=('_x', '_y'), copy=True, indicator=False,\n",
    "#          validate=None)\n",
    "# 可以实现类似 SQL 的 join 操作，参数为：\n",
    "\n",
    "# how：连接方式，默认为inner，可设为inner/outer/left/right\n",
    "# on：根据某个字段进行连接，必须存在于两个DateFrame中（若未同时存在，则需要分别使用left_on 和 right_on 来设置）\n",
    "# left_on：左连接，以DataFrame1中用作连接键的列\n",
    "# right_on：右连接，以DataFrame2中用作连接键的列\n",
    "# left_index：bool, default False，将DataFrame1行索引用作连接键\n",
    "# right_index：bool, default False，将DataFrame2行索引用作连接键\n",
    "# sort：根据连接键对合并后的数据进行排列，默认为True\n",
    "# suffixes：对两个数据集中出现的重复列，新数据集中加上后缀 _x, _y 进行区别"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38e64c15",
   "metadata": {},
   "source": [
    "#### how"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "489ac887",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# how 参数可以指定数据用哪种方法进行合并，没有的内容会为 NaN：\n",
    "\n",
    "# 以左为表基表\n",
    "pd.merge(left, right, how='left', on=['key1', 'key2'])\n",
    "\n",
    "# 以右为表基表\n",
    "pd.merge(left, right, how='right', on=['key1', 'key2'])\n",
    "\n",
    "# 取两个表的合集\n",
    "pd.merge(left, right, how='outer', on=['key1', 'key2'])\n",
    "\n",
    "# 取两个表的交集\n",
    "pd.merge(left, right, how='inner', on=['key1', 'key2'])\n",
    "\n",
    "\n",
    "# 下边是一个有重复连接键的例子：\n",
    "\n",
    "left = pd.DataFrame({'A': [1, 2], 'B': [2, 2]})\n",
    "right = pd.DataFrame({'A': [4, 5, 6], 'B': [2, 2, 2]})\n",
    "pd.merge(left, right, on='B', how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d926e3ab",
   "metadata": {},
   "source": [
    "#### validate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1430ca3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 检查重复键 validate\n",
    "\n",
    "# 一对多连接\n",
    "left = pd.DataFrame({'A' : [1,2], 'B' : [1, 2]})\n",
    "right = pd.DataFrame({'A' : [4,5,6], 'B': [2, 2, 2]})\n",
    "pd.merge(left, right, on='B', how='outer', validate=\"one_to_many\")\n",
    "\n",
    "# 其他的还有：\n",
    "\n",
    "# 如果指定，则检查合并是否为指定的类型：\n",
    "# “one_to_one” 或 “1:1”: 检查合并键在左右数据集中是否唯一\n",
    "# “one_to_many” 或 “1:m”: 检查合并键在左数据集中是否唯一\n",
    "# “many_to_one” 或 “m:1”: 检查合并键在右数据集中是否唯一\n",
    "# “many_to_many” 或 “m:m”: 允许，但不会检查"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "681095fb",
   "metadata": {},
   "source": [
    "#### indicator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3251ddd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 连接指示 连接指示 indicator\n",
    "# 如果设置 indicator 为 True, 则会增加名为 _merge 的一列，显示这列是多何而来，_merge 列有取三个值：\n",
    "\n",
    "# left_only 只在左表中\n",
    "# right_only 只在右表中\n",
    "# both 两个表中都有\n",
    "pd.merge(left, right, on='B', how='outer', indicator=True)\n",
    "\n",
    "'''\n",
    "   A_x  B  A_y     _merge\n",
    "0    1  1  NaN  left_only\n",
    "1    2  2  4.0       both\n",
    "2    2  2  5.0       both\n",
    "3    2  2  6.0       both\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "148778e0",
   "metadata": {},
   "source": [
    "### 单个连接键"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9af40c1d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T07:15:10.389804Z",
     "start_time": "2022-05-16T07:15:10.376408Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "\n",
    "# 数据准备\n",
    "AB = pd.DataFrame({'key': ['K1', 'K0', 'K2', 'K3'],\n",
    "                     'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                     'B': ['B0', 'B1', 'B2', 'B3']})\n",
    "\n",
    "CD = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],\n",
    "                      'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                      'D': ['D0', 'D1', 'D2', 'D3']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a10cf337",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K1</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K0</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   A   B\n",
       "0  K1  A0  B0\n",
       "1  K0  A1  B1\n",
       "2  K2  A2  B2\n",
       "3  K3  A3  B3"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3c0a3190",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   C   D\n",
       "0  K0  C0  D0\n",
       "1  K1  C1  D1\n",
       "2  K2  C2  D2\n",
       "3  K3  C3  D3"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "024e6ed4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T07:15:19.718927Z",
     "start_time": "2022-05-16T07:15:19.667290Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K1</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K0</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   A   B   C   D\n",
       "0  K1  A0  B0  C1  D1\n",
       "1  K0  A1  B1  C0  D0\n",
       "2  K2  A2  B2  C2  D2\n",
       "3  K3  A3  B3  C3  D3"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pd.merge(AB,CD) 或者 AB.merge(CD)\n",
    "pd.merge(AB,CD,how='left',on='key')  # on 为两个数据的连接键，都以 key 为标准。\n",
    "# AB.merge(CD, how='left', on='key')  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93cae33e",
   "metadata": {},
   "source": [
    "### 多个连接键"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "83ee6860",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T07:04:36.493794Z",
     "start_time": "2022-05-16T07:04:36.232768Z"
    }
   },
   "outputs": [],
   "source": [
    "# 数据准备\n",
    "AB = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                     'B': ['B0', 'B1', 'B2', 'B3']})\n",
    "\n",
    "CD = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                      'D': ['D0', 'D1', 'D2', 'D3']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "e6a05598",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T07:05:12.298286Z",
     "start_time": "2022-05-16T07:05:12.256711Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2   A   B   C   D\n",
       "0   K0   K0  A0  B0  C0  D0\n",
       "1   K1   K0  A2  B2  C1  D1\n",
       "2   K1   K0  A2  B2  C2  D2"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(AB,CD,how='inner',on=['key1','key2'])  # key1 和 key2 组合在两个表里共有的会被连接，两个表都有 (k1, k0) ，右表有两个，左表有一个，合并后右表共同了左表的内容。\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01303649",
   "metadata": {},
   "source": [
    "## pd.merge_ordered()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "56a072fd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T07:19:46.223358Z",
     "start_time": "2022-05-16T07:19:46.212991Z"
    }
   },
   "outputs": [],
   "source": [
    "# pd.merge_ordered 函数允许组合时间序列和其他有序数据。 \n",
    "# 特别是，它具有可选的fill_method 关键字来填充/插入丢失的数据："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1c53a5d7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T07:20:10.018589Z",
     "start_time": "2022-05-16T07:20:09.985748Z"
    }
   },
   "outputs": [],
   "source": [
    "# 数据准备\n",
    "left = pd.DataFrame({'k': ['K0', 'K1', 'K1', 'K2'],\n",
    "                     'lv': [1, 2, 3, 4],\n",
    "                     's': ['a', 'b', 'c', 'd']})\n",
    "\n",
    "right = pd.DataFrame({'k': ['K1', 'K2', 'K4'],\n",
    "                      'rv': [1, 2, 3]})\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2fb20c2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>k</th>\n",
       "      <th>lv</th>\n",
       "      <th>s</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>3</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K2</td>\n",
       "      <td>4</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    k  lv  s\n",
       "0  K0   1  a\n",
       "1  K1   2  b\n",
       "2  K1   3  c\n",
       "3  K2   4  d"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "9abb9f65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>k</th>\n",
       "      <th>rv</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    k  rv\n",
       "0  K1   1\n",
       "1  K2   2\n",
       "2  K4   3"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "c11d08f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>k</th>\n",
       "      <th>lv</th>\n",
       "      <th>s</th>\n",
       "      <th>rv</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>a</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>a</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>a</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>K2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>b</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>K4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>b</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>K1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>c</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>K2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>c</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>K4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>c</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>K1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>d</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>K2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>d</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>K4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>d</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     k   lv  s   rv\n",
       "0   K0  1.0  a  NaN\n",
       "1   K1  1.0  a  1.0\n",
       "2   K2  1.0  a  2.0\n",
       "3   K4  1.0  a  3.0\n",
       "4   K1  2.0  b  1.0\n",
       "5   K2  2.0  b  2.0\n",
       "6   K4  2.0  b  3.0\n",
       "7   K1  3.0  c  1.0\n",
       "8   K2  3.0  c  2.0\n",
       "9   K4  3.0  c  3.0\n",
       "10  K1  NaN  d  1.0\n",
       "11  K2  4.0  d  2.0\n",
       "12  K4  4.0  d  3.0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge_ordered(left, right, fill_method='ffill', left_by='s')  # 按s列排序"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9fe379b",
   "metadata": {},
   "source": [
    "## df.combine_first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "b4ad97fd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T07:24:51.802590Z",
     "start_time": "2022-05-16T07:24:51.780982Z"
    }
   },
   "outputs": [],
   "source": [
    "# 在数据合并过程中需要对应位置的数值进行计算，比如相加、平均，对空值补齐等，\n",
    "# Pandas 提供了 df.combine_first() 和 df. combine() 等方法进行这些操作。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eebde4c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.combine_first()\n",
    "# 使用相同位置的值更新空元素，它只能是 df1 有空元素时才能被替换，如果数据结构不一致，所得 DataFram e的行索引和列索引将是两者的并集。\n",
    "\n",
    "df1 = pd.DataFrame({'A': [None, 0], 'B': [None, 4]})\n",
    "df2 = pd.DataFrame({'A': [1, 1], 'B': [3, 3]})\n",
    "df1.combine_first(df2)\n",
    "'''\n",
    "     A    B\n",
    "0  1.0  3.0\n",
    "1  0.0  4.0\n",
    "'''\n",
    "# 在上例中，df1 中的 A 和 B 的空值被 df2 中的相同位置值替换。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14520426",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df. combine()\n",
    "# 可以与另一个 DataFrame 进行按列组合。使用函数将一个 DataFrame 与其他DataFrame合并，以逐元素合并列。 所得 DataFrame 的行索引和列索引将是两者的并集。\n",
    "\n",
    "# 这个函数中有两个参数，分别是两个 df 中对应的 series， 计算后返回一个 Series 或者标量。\n",
    "\n",
    "df1 = pd.DataFrame({'A': [0, 0], 'B': [4, 4]})\n",
    "df2 = pd.DataFrame({'A': [1, 1], 'B': [3, 3]})\n",
    "# s1 列总和如果小于 s2列总和取 s1, 否则取 s2\n",
    "take_smaller = lambda s1, s2: s1 if s1.sum() < s2.sum() else s2\n",
    "df1.combine(df2, take_smaller)\n",
    "'''\n",
    "A  B\n",
    "0  0  3\n",
    "1  0  3\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff4d4f29",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.update()\n",
    "# 使用来自另一个 DataFrame 的非NA值进行修改，原 df 为被更新。\n",
    "\n",
    "df = pd.DataFrame({'A': [1, 2, 3],\n",
    "                   'B': [400, 500, 600]})\n",
    "new_df = pd.DataFrame({'B': [4, 5, 6],\n",
    "                       'C': [7, 8, 9]})\n",
    "df.update(new_df)\n",
    "df\n",
    "   A  B\n",
    "0  1  4\n",
    "1  2  5\n",
    "2  3  6\n",
    "# DataFrame的长度不会增加，只会更新匹配的索引/列标签上的值。\n",
    "\n",
    "df = pd.DataFrame({'A': ['a', 'b', 'c'],\n",
    "                   'B': ['x', 'y', 'z']})\n",
    "new_df = pd.DataFrame({'B': ['d', 'e', 'f', 'g', 'h', 'i']})\n",
    "df.update(new_df)\n",
    "df\n",
    "   A  B\n",
    "0  a  d\n",
    "1  b  e\n",
    "2  c  f\n",
    "# 对于系列，必须设置其名称属性。\n",
    "\n",
    "df = pd.DataFrame({'A': ['a', 'b', 'c'],\n",
    "                   'B': ['x', 'y', 'z']})\n",
    "new_column = pd.Series(['d', 'e'], name='B', index=[0, 2])\n",
    "df.update(new_column)\n",
    "df\n",
    "   A  B\n",
    "0  a  d\n",
    "1  b  y\n",
    "2  c  e\n",
    "\n",
    "\n",
    "df = pd.DataFrame({'A': ['a', 'b', 'c'],\n",
    "                   'B': ['x', 'y', 'z']})\n",
    "new_df = pd.DataFrame({'B': ['d', 'e']}, index=[1, 2])\n",
    "df.update(new_df)\n",
    "df\n",
    "   A  B\n",
    "0  a  x\n",
    "1  b  d\n",
    "2  c  e\n",
    "# 如果其他包含NaN，则相应的值不会在原始数据帧中更新。\n",
    "\n",
    "df = pd.DataFrame({'A': [1, 2, 3],\n",
    "                   'B': [400, 500, 600]})\n",
    "new_df = pd.DataFrame({'B': [4, np.nan, 6]})\n",
    "df.update(new_df)\n",
    "df\n",
    "   A      B\n",
    "0  1    4.0\n",
    "1  2  500.0\n",
    "2  3    6.0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "211ddff0",
   "metadata": {},
   "source": [
    "## 数据对比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10be5acd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.compare() 和s.compare() 方法使您可以分别比较两个DataFrame 或 Series\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28699c52",
   "metadata": {},
   "source": [
    "### compare()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "699c77b3",
   "metadata": {},
   "source": [
    "#### 语法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f170f743",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.compare(other, align_axis=1, keep_shape=False, keep_equal=False)\n",
    "\n",
    "\"\"\"其中：\n",
    "other：被对比的数据\n",
    "align_axis=1：差异堆叠在列/行上\n",
    "keep_shape=False：不保留相等的值\n",
    "keep_equal=False：不保留所有原始行和列\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "371c0ca4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T09:19:55.621029Z",
     "start_time": "2022-05-16T09:19:55.583202Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\ndf\\n'''\\n  col1  col2  col3\\n0    a   1.0   1.0\\n1    a   2.0   2.0\\n2    b   3.0   3.0\\n3    b   NaN   4.0\\n4    a   5.0   5.0\\n'''\\n\""
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据准备\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"col1\": [\"a\", \"a\", \"b\", \"b\", \"a\"],\n",
    "        \"col2\": [1.0, 2.0, 3.0, np.nan, 5.0],\n",
    "        \"col3\": [1.0, 2.0, 3.0, 4.0, 5.0]\n",
    "    },\n",
    "    columns=[\"col1\", \"col2\", \"col3\"],\n",
    ")\n",
    "\n",
    "df\n",
    "\"\"\"\n",
    "df\n",
    "'''\n",
    "  col1  col2  col3\n",
    "0    a   1.0   1.0\n",
    "1    a   2.0   2.0\n",
    "2    b   3.0   3.0\n",
    "3    b   NaN   4.0\n",
    "4    a   5.0   5.0\n",
    "'''\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "c2576bf4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col1  col2  col3\n",
       "0    a   1.0   1.0\n",
       "1    a   2.0   2.0\n",
       "2    b   3.0   3.0\n",
       "3    b   NaN   4.0\n",
       "4    a   5.0   5.0"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "7f5aac04",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T09:20:19.192535Z",
     "start_time": "2022-05-16T09:20:19.179299Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\ndf2\\n'''\\n  col1  col2  col3\\n0    c   1.0   1.0\\n1    a   2.0   2.0\\n2    b   3.0   4.0\\n3    b   NaN   4.0\\n4    a   5.0   5.0\\n'''\\n\""
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对数据进行修改以便进行对比\n",
    "df2 = df.copy()\n",
    "df2.loc[0, 'col1'] = 'c'\n",
    "df2.loc[2, 'col3'] = 4.0\n",
    "\n",
    "\"\"\"\n",
    "df2\n",
    "'''\n",
    "  col1  col2  col3\n",
    "0    c   1.0   1.0\n",
    "1    a   2.0   2.0\n",
    "2    b   3.0   4.0\n",
    "3    b   NaN   4.0\n",
    "4    a   5.0   5.0\n",
    "'''\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "19eb2812",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col1  col2  col3\n",
       "0    c   1.0   1.0\n",
       "1    a   2.0   2.0\n",
       "2    b   3.0   4.0\n",
       "3    b   NaN   4.0\n",
       "4    a   5.0   5.0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "c7acbff3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T09:20:22.793580Z",
     "start_time": "2022-05-16T09:20:22.677807Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n  col1       col3\\n  self other self other\\n0    a     c  NaN   NaN\\n2  NaN   NaN  3.0   4.0\\n'"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 显示有差异的列\n",
    "# 默认情况下，如果两个对应的值相等，它们将显示为NaN。 此外，如果整个行/列中的所有值都将从结果中省略。 其余差异将在列上对齐\n",
    "df.compare(df2)\n",
    "\n",
    "'''\n",
    "  col1       col3\n",
    "  self other self other\n",
    "0    a     c  NaN   NaN\n",
    "2  NaN   NaN  3.0   4.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a0251e83",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">col1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">col3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col1       col3      \n",
       "  self other self other\n",
       "0    a     c  NaN   NaN\n",
       "2  NaN   NaN  3.0   4.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.compare(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "dee804bb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T09:22:48.721111Z",
     "start_time": "2022-05-16T09:22:48.689055Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n        col1  col3\\n0 self     a   NaN\\n  other    c   NaN\\n2 self   NaN   3.0\\n  other  NaN   4.0\\n'"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将差异堆叠在行上：\n",
    "\n",
    "df.compare(df2, align_axis=0)\n",
    "'''\n",
    "        col1  col3\n",
    "0 self     a   NaN\n",
    "  other    c   NaN\n",
    "2 self   NaN   3.0\n",
    "  other  NaN   4.0\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "79013b0d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T09:22:50.861860Z",
     "start_time": "2022-05-16T09:22:50.836390Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n  col1       col3\\n  self other self other\\n0    a     c  1.0   1.0\\n2    b     b  3.0   4.0\\n'"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 保留相等的值：\n",
    "\n",
    "df.compare(df2, keep_equal=True)\n",
    "'''\n",
    "  col1       col3\n",
    "  self other self other\n",
    "0    a     c  1.0   1.0\n",
    "2    b     b  3.0   4.0\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "577e238f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保留所有原始行和列：\n",
    "\n",
    "df.compare(df2, keep_shape=True)\n",
    "'''\n",
    "  col1       col2       col3\n",
    "  self other self other self other\n",
    "0    a     c  NaN   NaN  NaN   NaN\n",
    "1  NaN   NaN  NaN   NaN  NaN   NaN\n",
    "2  NaN   NaN  NaN   NaN  3.0   4.0\n",
    "3  NaN   NaN  NaN   NaN  NaN   NaN\n",
    "4  NaN   NaN  NaN   NaN  NaN   NaN\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "553b9ecf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保留所有原始行和列以及所有原始值：\n",
    "\n",
    "df.compare(df2, keep_shape=True, keep_equal=True)\n",
    "'''\n",
    "  col1       col2       col3\n",
    "  self other self other self other\n",
    "0    a     c  1.0   1.0  1.0   1.0\n",
    "1    a     a  2.0   2.0  2.0   2.0\n",
    "2    b     b  3.0   3.0  3.0   4.0\n",
    "3    b     b  NaN   NaN  4.0   4.0\n",
    "4    a     a  5.0   5.0  5.0   5.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c7d1a4c",
   "metadata": {},
   "source": [
    "### equals()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3266306",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"对比两个数据是否一致，测试两个对象是否包含相同的元素。\n",
    "\n",
    "此功能允许将两个Series或DataFrame相互比较，以查看它们是否具有相同的形状和元素。 \n",
    "相同位置的NaN被认为是相等的。 \n",
    "列标题不必具有相同的类型，但是列中的元素必须具有相同的dtype。\n",
    "\n",
    "此功能要求元素与其他Series或DataFrame中的元素具有相同的dtype。 但是，列标签不必具有相同的类型，只要它们仍被视为相等即可。\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1b70353",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df = pd.DataFrame({1: [10], 2: [20]})\n",
    "\"\"\"df\n",
    "    1   2\n",
    "0  10  20\"\"\"\n",
    "\n",
    "# DataFrames df和fully_equal的元素和列标签具有相同的类型和值，它们将返回True。\n",
    "\n",
    "exactly_equal = pd.DataFrame({1: [10], 2: [20]})\n",
    "\"\"\"exactly_equal\n",
    "'''\n",
    "    1   2\n",
    "0  10  20\n",
    "'''\"\"\"\n",
    "df.equals(exactly_equal)\n",
    "# True\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3aa9dbb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrames df和different_column_type具有相同的元素类型和值，但列标签具有不同的类型，它们仍将返回True。\n",
    "\n",
    "different_column_type = pd.DataFrame({1.0: [10], 2.0: [20]})\n",
    "\"\"\"different_column_type\n",
    "'''\n",
    "   1.0  2.0\n",
    "0   10   20\n",
    "'''\"\"\"\n",
    "df.equals(different_column_type)\n",
    "# True\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a309464d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrames df和different_data_type为其元素的相同值具有不同的类型，即使它们的列标签具有相同的值和类型，它们也将返回False。\n",
    "\n",
    "different_data_type = pd.DataFrame({1: [10.0], 2: [20.0]})\n",
    "\"\"\"different_data_type\n",
    "'''\n",
    "      1     2\n",
    "0  10.0  20.0\n",
    "'''\"\"\"\n",
    "df.equals(different_data_type)\n",
    "# False"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d03a2210",
   "metadata": {},
   "source": [
    "### align() 将结构不一样的DF对齐"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e844c6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrame.align 不会组合两个数据帧，但是可以将它们对齐，以便两个数据帧具有相同的行和/或列配置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "9af022f5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T09:46:00.967956Z",
     "start_time": "2022-05-16T09:46:00.942538Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"df2\\n'''\\n     A    B    C    D\\n2   10   20   30   40\\n3   60   70   80   90\\n4  600  700  800  900\\n'''\""
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "df1 = pd.DataFrame([[1,2,3,4], [6,7,8,9]], \n",
    "                   columns=['D', 'B', 'E', 'A'],\n",
    "                   index=[1,2])\n",
    "\n",
    "df2 = pd.DataFrame([[10,20,30,40], [60,70,80,90], [600,700,800,900]],\n",
    "                   columns=['A', 'B', 'C', 'D'],\n",
    "                   index=[2,3,4])\n",
    "\n",
    "\"\"\"df1\n",
    "'''\n",
    "   D  B  E  A\n",
    "1  1  2  3  4\n",
    "2  6  7  8  9\n",
    "'''\"\"\"\n",
    "\n",
    "\"\"\"df2\n",
    "'''\n",
    "     A    B    C    D\n",
    "2   10   20   30   40\n",
    "3   60   70   80   90\n",
    "4  600  700  800  900\n",
    "'''\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "b463860d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-05-16T09:46:45.064894Z",
     "start_time": "2022-05-16T09:46:45.041105Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B   C  D  E\n",
      "1  4  2 NaN  1  3\n",
      "2  9  7 NaN  6  8\n",
      "     A    B    C    D   E\n",
      "2   10   20   30   40 NaN\n",
      "3   60   70   80   90 NaN\n",
      "4  600  700  800  900 NaN\n"
     ]
    }
   ],
   "source": [
    "# 让我们对齐这两个数据帧，按列对齐（axis=1），并对列标签执行外部联接（join='outer'）：\n",
    "\n",
    "a1, a2 = df1.align(df2, join='outer', axis=1)\n",
    "\n",
    "print(a1)\n",
    "\"\"\"\n",
    "   A  B   C  D  E\n",
    "1  4  2 NaN  1  3\n",
    "2  9  7 NaN  6  8\n",
    "\"\"\"\n",
    "\n",
    "print(a2)\n",
    "\"\"\"\n",
    "     A    B    C    D   E\n",
    "2   10   20   30   40 NaN\n",
    "3   60   70   80   90 NaN\n",
    "4  600  700  800  900 NaN\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bcfb12a",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"df1中的列已重新排列，以便与df2中的列对齐。\n",
    "有一个标记为“C”的列已添加到df1，还有一个标记为“E”的列已添加到df2。这些列已用NaN填充。这是因为我们对列标签执行了外部联接。\n",
    "数据帧内的所有值均未更改。\n",
    "行标签未对齐；df2有第3行和第4行，而df1没有。这是因为我们要求对列进行对齐（轴=1）。\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "239a3f77",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 如果我们在行和列上对齐，但将join参数更改为“right”,结果会不一样\n",
    "\n",
    "a1, a2 = df1.align(df2, join='right', axis=None)\n",
    "print(a1)\n",
    "print(a2)\n",
    "'''\n",
    "     A    B   C    D\n",
    "2  9.0  7.0 NaN  6.0\n",
    "3  NaN  NaN NaN  NaN\n",
    "4  NaN  NaN NaN  NaN\n",
    "\n",
    "     A    B    C    D\n",
    "2   10   20   30   40\n",
    "3   60   70   80   90\n",
    "4  600  700  800  900\n",
    "'''\n",
    "# 总之，如果要确保两个数据帧之间的行和/或列的排列相同，而不改变两个数据帧中包含的任何数据，请使用 DataFrame.align()。"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "255.788px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
